#!/usr/bin/env/python3.7
# -*- coding: utf-8 -*-

"""
@File    : app_request_mysql.py
@Time    : 11/16/2021
@Author  : Silence
@Version : 1.0
@Contact : silence2021silence@163.com
@License : (C)Copyright 2021-2021, Silence
@Desc    : None
"""

import datetime
import pymysql


database = pymysql.connect(host="120.24.51.156", user="dormitory_system", password="123456",
                                    database="dormitory_system", charset="utf8")


class Data():


    # -----考勤部分----- #
    # -----数据总览----- #
    def data_overview(type_1, type_2):
        cursor = database.cursor()
        sql_1 = """
                select 
                    count(distinct %s) as count
                from 
                    log_data
                """

        sql_2 = """
                    select
        	            count(distinct %s) as count
                    from
        	            log_data
                    where
                        status = '%s'
                """
        if type_2 == 'all':
            sql_3 = sql_1 % type_1
            cursor.execute(sql_3)
        else:
            sql_4 = sql_2 % (type_1, type_2)
            cursor.execute(sql_4)
        result = cursor.fetchall()
        number = str(result).replace('(', '').replace(')', '').replace(',', '')
        number = int(number)
        return number




    # -----出勤情况----- #

    # -----全部寝室----- #

# -----某寝室出勤的所有人信息----- #
    def data_16(status):
        cursor = database.cursor()
        sql_1 = """
                select 
                    id, 
        	        student_number, 
        	        name, 
        	        room,
        	        grade, 
        	        class, 
        	        ip_address, 
        	        status, 
        	        status_datetime 
                from 
        	        log_data 
        	    where
        	        status = '%s'
                """
        sql_2 = sql_1 % status
        sql_3 = """
                select 
                    id, 
        	        student_number, 
        	        name, 
        	        room,
        	        grade, 
        	        class, 
        	        ip_address, 
        	        status, 
        	        status_datetime 
                from 
        	        log_data 
                """
        if status == '全部':
            cursor.execute(sql_3)
        else:
            cursor.execute(sql_2)
        result = cursor.fetchall()

        id = []
        student_number = []
        name = []
        room = []
        grade = []
        class_ = []
        ip_address = []
        status = []
        status_datetime = []

        for r in result:
            id.append(r[0])
            student_number.append(r[1])
            name.append(r[2])
            room.append(r[3])
            grade.append(str(r[4]))
            class_.append(r[5])
            ip_address.append(r[6])
            status.append(r[7])
            status_datetime.append(r[8])
        return (id, student_number, name, room, grade, class_, ip_address, status, status_datetime)


# -----违纪寝室----- #
    def data_21():
        cursor = database.cursor()
        sql = """
                select 
                    distinct room
                from 
                    violating_discipline
                """
        cursor.execute(sql)
        result = cursor.fetchall()
        list = []
        for r in result:
            s = str(r).strip('(').strip(')').strip(',').strip("'")
            list.append(s)
        return list

# -----某寝室的违纪所有人信息----- #
    def data_22(clicked_room_number):
        cursor = database.cursor()
        sql_s = """
                select 
                    id, 
        	        student_number, 
        	        name, 
        	        room,
        	        grade, 
        	        class, 
        	        violating_discipline_status, 
        	        violating_discipline_status_start_time, 
        	        violating_discipline_status_duration, 
        	        ip_address 
                from 
        	        violating_discipline 
                where 
        	        room = '%s'
                """
        room = str(clicked_room_number)
        sql = sql_s % room
        cursor.execute(sql)
        result = cursor.fetchall()

        id = []
        student_number = []
        name = []
        room = []
        grade = []
        class_ = []
        violating_discipline_status = []
        violating_discipline_status_start_time = []
        violating_discipline_status_duration = []
        ip_address = []

        for r in result:
            id.append(r[0])
            student_number.append(r[1])
            name.append(r[2])
            room.append(r[3])
            grade.append(str(r[4]))
            class_.append(r[5])
            violating_discipline_status.append(r[6])
            violating_discipline_status_start_time.append(str(r[7]))
            violating_discipline_status_duration.append(r[8])
            ip_address.append(r[9])
        return (id, student_number, name, room, grade, class_,
                violating_discipline_status, violating_discipline_status_start_time,
                violating_discipline_status_duration, ip_address)

# -----语音留言列表----- #
    def data_23():
        cursor = database.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
                select
        	        id, 
        	        student_number, 
        	        name, 
        	        room,
        	        grade, 
        	        class, 
        	        upload_datetime, 
        	        ip_address
                from
        	        student_audio_message

                """
        cursor.execute(sql)
        result = cursor.fetchall()

        list = []
        for r in result:
            id_ = r["id"]
            student_number = r["student_number"]
            name = r["name"]
            room = r["room"]
            grade = r["grade"]
            class_ = r["class"]
            upload_datetime = r["upload_datetime"]
            ip_address = r["ip_address"]
            list.append('语音留言' + str(id_) + ' -----> ' + room + '室 ' + name)
        return list

# -----根据点击事件下载音频文件----- #
    def data_24(clicked_speaker_id, clicked_speaker_name):
        cursor = database.cursor(cursor=pymysql.cursors.DictCursor)
        sql_s = """
                select
                	room, name, audio_file_data, audio_file_type
                from
                	student_audio_message
                where
                    id = '%s' and name = '%s'

                    """
        sql = sql_s % (int(clicked_speaker_id), clicked_speaker_name)
        print(sql)
        cursor.execute(sql)
        audio_file = cursor.fetchall()
        for data in audio_file:
            file_room = data["room"]
            file_name = data["name"]
            file_data = data["audio_file_data"]
            file_type = data["audio_file_type"]
            with open("./download_audio/" + file_room + '室' + file_name + "." + file_type, "wb") as f:
                f.write(file_data)



if __name__ == "__main__":
    Data.data_24()


